﻿using System.Data;
using System.Globalization;
using System.IO;
using System.Web.UI.HtmlControls;
using H2.NET.Common;
using H2.NET.Data.Common;
using H2.NET.Web.Management.Config.Service;
using H2.NET.Web.Pages.SMD.Model;
using H2.NET.Web.Workflow.DwzCode;
using System;
using System.Collections.Generic;
using NPOI.HSSF.UserModel;

namespace H2.NET.Web.Pages.SMD
{
    public partial class StoreTrackingReport : BasePage
    {
        public DataTable DdcnR;
        public DataTable DdcnC;
        public DataTable DcssR;
        public DataTable DcssC;
        public DataTable DdcnR1;
        public DataTable DdcnC1;
        public DataTable DcssR1;
        public DataTable DcssC1;
 
        protected void Page_Load(object sender, EventArgs e)
        {
           
            var action = AksRequest.GetQueryString("action");
            if (action == "export")
            {
                Export();
            }
            LoadDataTable();
        }

        public void LoadDataTable()
        {
            var year = Utils.StrToInt(AksRequest.GetFormString("year"), DateTime.Now.Year);
            var month = Utils.StrToInt(AksRequest.GetFormString("month"), DateTime.Now.Month);

            yearSearch.Value = year.ToString();
            monthSearch.Value = month.ToString();

            var config = new ConfigService();
            DdcnR = config.GetList("ParentId = (select configid from t_smd_Config where code='StoreTtack_r_ddcn_h') order by seq").Tables[0];
            DdcnC = config.GetList("ParentId = (select configid from t_smd_Config where code='StoreTtack_c_ddcn_h') order by seq").Tables[0];
            DcssR = config.GetList("ParentId = (select configid from t_smd_Config where code='StoreTtack_r_dcss_h') order by seq").Tables[0];
            DcssC = config.GetList("ParentId = (select configid from t_smd_Config where code='StoreTtack_c_dcss_h') order by seq").Tables[0];

            clsCmm.clsDB.Storage("smdDB");
            var ddcnRSql = "select * from (select a.Region Cluster,a.[before_amount] before_r_ddcn,isnull(c.[now_amount],0) as new_r_ddcn,isnull(d.[up_dcss],0) u_dcss_r_ddcn,isnull(e.[up_dis],0) as u_dis_r_ddcn,isnull(f.[all_amount],0) as ttl_r_ddcn,isnull(g.[close_amount],0) as close_r_ddcn,isnull(b.[now_all_Amount],0) as now_r_ddcn from (select count(1) as 'before_amount',Region from t_smd_Store s where s.StoreType = 'ddcn' and (year(s.CurrentStartDate) < (case '" + year + "' when '' then year(GETDATE()) else '" + year + "' end) or (YEAR(s.CurrentStartDate) = (case '" + year + "' when '' then year(GETDATE()) else '" + year + "' end) and MONTH(s.CurrentStartDate) < case '" + (month - 1) + "' when '' then MONTH(GETDATE()) else '" + (month - 1) + "' END)) and s.StoreStatus != '关闭' group by Region ) as a left join ( select count(1) as 'now_all_Amount',Region from t_smd_Store s where s.StoreType = 'ddcn' and  (year(s.CurrentStartDate) < (case '" + year + "' when '' then year(GETDATE()) else '" + year + "' end)  or (YEAR(s.CurrentStartDate) = (case '" + year + "' when '' then year(GETDATE()) else '" + year + "' end)  and MONTH(s.CurrentStartDate) < case '" + month + "' when '' then MONTH(GETDATE()) else '" + month + "' END)) and s.StoreStatus != '关闭' group by Region ) as b on a.region =b.region left JOIN (select count(1) as 'now_amount',Region from t_smd_Store s where s.StoreType = 'ddcn' and s.IsUpgraded = 0 and s.IsDowngraded = 0 and YEAR(s.CurrentStartDate) = '" + year + "' and s.StoreStatus != '关闭' and MONTH(s.CurrentStartDate) = '" + (month - 1) + "' group by Region ) as c on a.region = c.Region left join  (select count(1) as 'up_dcss',Region from t_smd_Store s where s.StoreType = 'ddcn' and s.IsUpgraded = 1 and s.IsDowngraded = 0 and s.BeforeUpgradedType = 'DCSS' and YEAR(s.CurrentStartDate) = '" + year + "' and MONTH(s.CurrentStartDate) = '" + (month - 1) + "'  group by Region ) as d on a.Region = d.Region left join (select count(1) as 'up_dis',Region from t_smd_Store s where s.StoreType = 'ddcn' and s.IsUpgraded = 1 and s.IsDowngraded = 0 and s.BeforeUpgradedType = 'DIS' and YEAR(s.CurrentStartDate) = '" + year + "' and MONTH(s.CurrentStartDate) = '" + (month - 1) + "'  group by Region ) as e on a.Region = e.Region  left JOIN  (select count(1) as 'all_amount',Region from t_smd_Store s where s.StoreType = 'ddcn' and YEAR(s.CurrentStartDate) = '" + year + "' and MONTH(s.CurrentStartDate) = '" + (month - 1) + "'  group by Region ) as f on a.Region = f.Region left JOIN (select count(1) as 'close_amount',Region from t_smd_Store s where s.StoreType = 'ddcn' and s.StoreStatus = '关闭' and YEAR(s.CurrentStartDate) = '" + year + "' and MONTH(s.CurrentStartDate) = '" + (month - 1) + "'  group by Region ) as g on a.Region = g.Region ) as a  UNION ALL   select 'PRC' as region,SUM(a.[before_amount]) as [before_amount], SUM(a.[now_amount]) as [now_amount], SUM(a.[up_dcss]) as [up_dcss],SUM(a.[up_dis]) AS [up_dis], SUM(a.[all_amount]) as [all_amount], SUM(a.[close_amount]) as [close_amount], SUM(a.[now_all_Amount]) as [now_all_Amount] from (select a.Region,a.[before_amount],isnull(c.[now_amount],0) as [now_amount],isnull(d.[up_dcss],0) [up_dcss],isnull(e.[up_dis],0) as [up_dis],isnull(f.[all_amount],0) as [all_amount],isnull(g.[close_amount],0) as [close_amount],isnull(b.[now_all_Amount],0) now_all_Amount from ( select count(1) as 'before_amount',Region from t_smd_Store s where s.StoreType = 'ddcn' and  (year(s.CurrentStartDate) < (case '" + year + "' when '' then year(GETDATE()) else '" + year + "' end)  or (YEAR(s.CurrentStartDate) = (case '" + year + "' when '' then year(GETDATE()) else '" + year + "' end)  and MONTH(s.CurrentStartDate) < case '" + (month - 1) + "' when '' then MONTH(GETDATE()) else '" + (month - 1) + "' END)) and s.StoreStatus != '关闭' group by Region ) as a left join ( select count(1) as 'now_all_Amount',Region from t_smd_Store s where s.StoreType = 'ddcn' and  (year(s.CurrentStartDate) < (case '" + year + "' when '' then year(GETDATE()) else '" + year + "' end)  or (YEAR(s.CurrentStartDate) = (case '" + year + "' when '' then year(GETDATE()) else '" + year + "' end)  and MONTH(s.CurrentStartDate) < case '" + month + "' when '' then MONTH(GETDATE()) else '" + month + "' END)) and s.StoreStatus != '关闭' group by Region ) as b on a.region =b.region left JOIN (select count(1) as 'now_amount',Region from t_smd_Store s where s.StoreType = 'ddcn' and s.IsUpgraded = 0 and s.IsDowngraded = 0 and YEAR(s.CurrentStartDate) = '" + year + "' and s.StoreStatus != '关闭' and MONTH(s.CurrentStartDate) = '" + (month - 1) + "' group by Region ) as c on a.region = c.Region left join  (select count(1) as 'up_dcss',Region from t_smd_Store s where s.StoreType = 'ddcn' and s.IsUpgraded = 1 and s.IsDowngraded = 0 and s.BeforeUpgradedType = 'DCSS' and YEAR(s.CurrentStartDate) = '" + year + "' and MONTH(s.CurrentStartDate) = '" + (month - 1) + "'  group by Region ) as d on a.Region = d.Region left join (select count(1) as 'up_dis',Region from t_smd_Store s where s.StoreType = 'ddcn' and s.IsUpgraded = 1 and s.IsDowngraded = 0 and s.BeforeUpgradedType = 'dis' and YEAR(s.CurrentStartDate) = '" + year + "' and MONTH(s.CurrentStartDate) = '" + (month - 1) + "'  group by Region ) as e on a.Region = e.Region  left JOIN (select count(1) as 'all_amount',Region from t_smd_Store s where s.StoreType = 'ddcn' and YEAR(s.CurrentStartDate) = '" + year + "' and MONTH(s.CurrentStartDate) = '" + (month - 1) + "'  group by Region ) as f on a.Region = f.Region left JOIN (select count(1) as 'close_amount',Region from t_smd_Store s where s.StoreType = 'ddcn' and s.StoreStatus = '关闭' and YEAR(s.CurrentStartDate) = '" + year + "' and MONTH(s.CurrentStartDate) = '" + (month - 1) + "'  group by Region ) as g on a.Region = g.Region ) as a;";
            DdcnR1 = clsCmm.clsDB.Query(ddcnRSql);
            var ddcnCSql = "select * from (select a.cityClass Cluster,a.[before_amount] before_c_ddcn,isnull(c.[now_amount],0) as new_c_ddcn,isnull(d.[up_dcss],0) u_dcss_c_ddcn,isnull(e.[up_dis],0) as u_dis_c_ddcn,isnull(f.[all_amount],0) as ttl_c_ddcn,isnull(g.[close_amount],0) as close_c_ddcn,isnull(b.[now_all_Amount],0) now_c_ddcn from ( select count(1) as 'before_amount',cityClass from t_smd_Store s where s.StoreType = 'ddcn' and  (year(s.CurrentStartDate) < (case '" + year + "' when '' then year(GETDATE()) else '" + year + "' end)  or (YEAR(s.CurrentStartDate) = (case '" + year + "' when '' then year(GETDATE()) else '" + year + "' end)  and MONTH(s.CurrentStartDate) < case '" + (month - 1) + "' when '' then MONTH(GETDATE()) else '" + (month - 1) + "' END)) and s.StoreStatus != '关闭' group by cityClass ) as a left join ( select count(1) as 'now_all_Amount',cityClass from t_smd_Store s where s.StoreType = 'ddcn' and  (year(s.CurrentStartDate) < (case '" + year + "' when '' then year(GETDATE()) else '" + year + "' end)  or (YEAR(s.CurrentStartDate) = (case '" + year + "' when '' then year(GETDATE()) else '" + year + "' end)  and MONTH(s.CurrentStartDate) < case '" + month + "' when '' then MONTH(GETDATE()) else '" + month + "' END)) and s.StoreStatus != '关闭' group by cityClass ) as b on a.cityClass =b.cityClass left JOIN (select count(1) as 'now_amount',cityClass from t_smd_Store s where s.StoreType = 'ddcn' and s.IsUpgraded = 0 and s.IsDowngraded = 0 and YEAR(s.CurrentStartDate) = '" + year + "' and s.StoreStatus != '关闭' and MONTH(s.CurrentStartDate) = '" + (month - 1) + "' group by cityClass ) as c on a.cityClass = c.cityClass left join  (select count(1) as 'up_dcss',cityClass from t_smd_Store s where s.StoreType = 'ddcn' and s.IsUpgraded = 1 and s.IsDowngraded = 0 and s.BeforeUpgradedType = 'DCSS' and YEAR(s.CurrentStartDate) = '" + year + "' and MONTH(s.CurrentStartDate) = '" + (month - 1) + "'  group by cityClass ) as d on a.cityClass = d.cityClass left join (select count(1) as 'up_dis',cityClass from t_smd_Store s where s.StoreType = 'ddcn' and s.IsUpgraded = 1 and s.IsDowngraded = 0 and s.BeforeUpgradedType = 'DIS' and YEAR(s.CurrentStartDate) = '" + year + "' and MONTH(s.CurrentStartDate) = '" + (month - 1) + "'  group by cityClass ) as e on a.cityClass = e.cityClass  left JOIN  (select count(1) as 'all_amount',cityClass from t_smd_Store s where s.StoreType = 'ddcn' and YEAR(s.CurrentStartDate) = '" + year + "' and MONTH(s.CurrentStartDate) = '" + (month - 1) + "'  group by cityClass ) as f on a.cityClass = f.cityClass left JOIN (select count(1) as 'close_amount',cityClass from t_smd_Store s where s.StoreType = 'ddcn' and s.StoreStatus = '关闭' and YEAR(s.CurrentStartDate) = '" + year + "' and MONTH(s.CurrentStartDate) = '" + (month - 1) + "'  group by cityClass ) as g on a.cityClass = g.cityClass ) as a  UNION ALL   select 'PRC' as cityClass,SUM(a.[before_amount]) as [before_amount], SUM(a.[now_amount]) as [now_amount], SUM(a.[up_dcss]) as [up_dcss],SUM(a.[up_dis]) AS [up_dis], SUM(a.[all_amount]) as [all_amount], SUM(a.[close_amount]) as [close_amount], SUM(a.[now_all_Amount]) as [now_all_Amount] from (select a.cityClass,a.[before_amount],isnull(c.[now_amount],0) as [now_amount],isnull(d.[up_dcss],0) [up_dcss],isnull(e.[up_dis],0) as [up_dis],isnull(f.[all_amount],0) as [all_amount],isnull(g.[close_amount],0) as [close_amount],isnull(b.[now_all_Amount],0) now_all_Amount from ( select count(1) as 'before_amount',cityClass from t_smd_Store s where s.StoreType = 'ddcn' and  (year(s.CurrentStartDate) < (case '" + year + "' when '' then year(GETDATE()) else '" + year + "' end)  or (YEAR(s.CurrentStartDate) = (case '" + year + "' when '' then year(GETDATE()) else '" + year + "' end)  and MONTH(s.CurrentStartDate) < case '" + (month - 1) + "' when '' then MONTH(GETDATE()) else '" + (month - 1) + "' END)) and s.StoreStatus != '关闭' group by cityClass ) as a left join ( select count(1) as 'now_all_Amount',cityClass from t_smd_Store s where s.StoreType = 'ddcn' and  (year(s.CurrentStartDate) < (case '" + year + "' when '' then year(GETDATE()) else '" + year + "' end)  or (YEAR(s.CurrentStartDate) = (case '" + year + "' when '' then year(GETDATE()) else '" + year + "' end)  and MONTH(s.CurrentStartDate) < case '" + month + "' when '' then MONTH(GETDATE()) else '" + month + "' END)) and s.StoreStatus != '关闭' group by cityClass ) as b on a.cityClass =b.cityClass left JOIN (select count(1) as 'now_amount',cityClass from t_smd_Store s where s.StoreType = 'ddcn' and s.IsUpgraded = 0 and s.IsDowngraded = 0 and YEAR(s.CurrentStartDate) = '" + year + "' and s.StoreStatus != '关闭' and MONTH(s.CurrentStartDate) = '" + (month - 1) + "' group by cityClass ) as c on a.cityClass = c.cityClass left join  (select count(1) as 'up_dcss',cityClass from t_smd_Store s where s.StoreType = 'ddcn' and s.IsUpgraded = 1 and s.IsDowngraded = 0 and s.BeforeUpgradedType = 'DCSS' and YEAR(s.CurrentStartDate) = '" + year + "' and MONTH(s.CurrentStartDate) = '" + (month - 1) + "'  group by cityClass ) as d on a.cityClass = d.cityClass left join (select count(1) as 'up_dis',cityClass from t_smd_Store s where s.StoreType = 'ddcn' and s.IsUpgraded = 1 and s.IsDowngraded = 0 and s.BeforeUpgradedType = 'dis' and YEAR(s.CurrentStartDate) = '" + year + "' and MONTH(s.CurrentStartDate) = '" + (month - 1) + "'  group by cityClass ) as e on a.cityClass = e.cityClass  left JOIN (select count(1) as 'all_amount',cityClass from t_smd_Store s where s.StoreType = 'ddcn' and YEAR(s.CurrentStartDate) = '" + year + "' and MONTH(s.CurrentStartDate) = '" + (month - 1) + "'  group by cityClass ) as f on a.cityClass = f.cityClass left JOIN (select count(1) as 'close_amount',cityClass from t_smd_Store s where s.StoreType = 'ddcn' and s.StoreStatus = '关闭' and YEAR(s.CurrentStartDate) = '" + year + "' and MONTH(s.CurrentStartDate) = '" + (month - 1) + "'  group by cityClass ) as g on a.cityClass = g.cityClass ) as a;";
            DdcnC1 = clsCmm.clsDB.Query(ddcnCSql);
            var dcssRSql = "select * from (select a.Region Cluster,a.[before_amount] before_r_dcss,isnull(c.[now_amount],0) as new_r_dcss ,isnull(d.[dwon_ddcn],0) d_ddcn_r_dcss,isnull(e.[up_dis],0) as u_dis_r_dcss,isnull(f.[all_amount],0) as ttl_r_dcss,isnull(g.[close_amount],0) as close_r_dcss,isnull(b.[now_all_Amount],0) now_r_dcss from (select count(1) as 'before_amount',Region from t_smd_Store s where s.StoreType = 'dcss' and  (year(s.CurrentStartDate) < (case '" + year + "' when '' then year(GETDATE()) else '" + year + "' end)  or (YEAR(s.CurrentStartDate) = (case '" + year + "' when '' then year(GETDATE()) else '" + year + "' end)  and MONTH(s.CurrentStartDate) < case '" + (month - 1) + "' when '' then MONTH(GETDATE()) else '" + (month - 1) + "' END)) and s.StoreStatus != '关闭' group by Region ) as a left join ( select count(1) as 'now_all_Amount',Region from t_smd_Store s where s.StoreType = 'dcss' and  (year(s.CurrentStartDate) < (case '" + year + "' when '' then year(GETDATE()) else '" + year + "' end)  or (YEAR(s.CurrentStartDate) = (case '" + year + "' when '' then year(GETDATE()) else '" + year + "' end)  and MONTH(s.CurrentStartDate) < case '" + month + "' when '' then MONTH(GETDATE()) else '" + month + "' END)) and s.StoreStatus != '关闭' group by Region ) as b on a.region =b.region left JOIN (select count(1) as 'now_amount',Region from t_smd_Store s where s.StoreType = 'dcss' and s.IsUpgraded = 0 and s.IsDowngraded = 0 and YEAR(s.CurrentStartDate) = '" + year + "' and s.StoreStatus != '关闭' and MONTH(s.CurrentStartDate) = '" + (month - 1) + "' group by Region ) as c on a.region = c.Region left join  (select count(1) as 'dwon_ddcn',Region from t_smd_Store s where s.StoreType = 'dcss' and s.IsUpgraded = 0 and s.IsDowngraded = 1 and s.BeforeDowngradedType = 'ddcn' and YEAR(s.CurrentStartDate) = '" + year + "' and MONTH(s.CurrentStartDate) = '" + (month - 1) + "'  group by Region ) as d on a.Region = d.Region left join (select count(1) as 'up_dis',Region from t_smd_Store s where s.StoreType = 'dcss' and s.IsUpgraded = 1 and s.IsDowngraded = 0 and s.BeforeUpgradedType = 'dis' and YEAR(s.CurrentStartDate) = '" + year + "' and MONTH(s.CurrentStartDate) = '" + (month - 1) + "'  group by Region ) as e on a.Region = e.Region  left JOIN  (select count(1) as 'all_amount',Region from t_smd_Store s where s.StoreType = 'dcss' and YEAR(s.CurrentStartDate) = '" + year + "' and MONTH(s.CurrentStartDate) = '" + (month - 1) + "'  group by Region ) as f on a.Region = f.Region left JOIN (select count(1) as 'close_amount',Region from t_smd_Store s where s.StoreType = 'dcss' and s.StoreStatus = '关闭' and YEAR(s.CurrentStartDate) = '" + year + "' and MONTH(s.CurrentStartDate) = '" + (month - 1) + "'  group by Region ) as g on a.Region = g.Region ) as a  UNION ALL   select 'PRC' as region,SUM(a.[before_amount]) as [before_amount], SUM(a.[now_amount]) as [now_amount], SUM(a.[dwon_ddcn]) as [dwon_ddcn],SUM(a.[up_dis]) AS [up_dis], SUM(a.[all_amount]) as [all_amount], SUM(a.[close_amount]) as [close_amount], SUM(a.[now_all_Amount]) as [now_all_Amount] from (select a.Region,a.[before_amount],isnull(c.[now_amount],0) as [now_amount],isnull(d.[dwon_ddcn],0) [dwon_ddcn],isnull(e.[up_dis],0) as [up_dis],isnull(f.[all_amount],0) as [all_amount],isnull(g.[close_amount],0) as [close_amount],isnull(b.[now_all_Amount],0) now_all_Amount from ( select count(1) as 'before_amount',Region from t_smd_Store s where s.StoreType = 'dcss' and  (year(s.CurrentStartDate) < (case '" + year + "' when '' then year(GETDATE()) else '" + year + "' end)  or (YEAR(s.CurrentStartDate) = (case '" + year + "' when '' then year(GETDATE()) else '" + year + "' end)  and MONTH(s.CurrentStartDate) < case '" + (month - 1) + "' when '' then MONTH(GETDATE()) else '" + (month - 1) + "' END)) and s.StoreStatus != '关闭' group by Region ) as a left join ( select count(1) as 'now_all_Amount',Region from t_smd_Store s where s.StoreType = 'dcss' and  (year(s.CurrentStartDate) < (case '" + year + "' when '' then year(GETDATE()) else '" + year + "' end)  or (YEAR(s.CurrentStartDate) = (case '" + year + "' when '' then year(GETDATE()) else '" + year + "' end)  and MONTH(s.CurrentStartDate) < case '" + month + "' when '' then MONTH(GETDATE()) else '" + month + "' END)) and s.StoreStatus != '关闭' group by Region ) as b on a.region =b.region left JOIN (select count(1) as 'now_amount',Region from t_smd_Store s where s.StoreType = 'dcss' and s.IsUpgraded = 0 and s.IsDowngraded = 0 and YEAR(s.CurrentStartDate) = '" + year + "' and s.StoreStatus != '关闭' and MONTH(s.CurrentStartDate) = '" + (month - 1) + "' group by Region ) as c on a.region = c.Region left join  (select count(1) as 'dwon_ddcn',Region from t_smd_Store s where s.StoreType = 'dcss' and s.IsUpgraded = 0 and s.IsDowngraded = 1 and s.BeforeDowngradedType = 'ddcn' and YEAR(s.CurrentStartDate) = '" + year + "' and MONTH(s.CurrentStartDate) = '" + (month - 1) + "'  group by Region ) as d on a.Region = d.Region left join (select count(1) as 'up_dis',Region from t_smd_Store s where s.StoreType = 'dcss' and s.IsUpgraded = 1 and s.IsDowngraded = 0 and s.BeforeUpgradedType = 'dis' and YEAR(s.CurrentStartDate) = '" + year + "' and MONTH(s.CurrentStartDate) = '" + (month - 1) + "'  group by Region ) as e on a.Region = e.Region  left JOIN (select count(1) as 'all_amount',Region from t_smd_Store s where s.StoreType = 'dcss' and YEAR(s.CurrentStartDate) = '" + year + "' and MONTH(s.CurrentStartDate) = '" + (month - 1) + "'  group by Region ) as f on a.Region = f.Region left JOIN (select count(1) as 'close_amount',Region from t_smd_Store s where s.StoreType = 'dcss' and s.StoreStatus = '关闭' and YEAR(s.CurrentStartDate) = '" + year + "' and MONTH(s.CurrentStartDate) = '" + (month - 1) + "'  group by Region ) as g on a.Region = g.Region ) as a;";
            DcssR1 = clsCmm.clsDB.Query(dcssRSql);
            var dcssCSql = "select * from (select a.cityClass Cluster,a.[before_amount] before_c_dcss,isnull(c.[now_amount],0) as new_c_dcss,isnull(d.[dwon_ddcn],0) d_ddcn_c_dcss,isnull(e.[up_dis],0) as u_dis_c_dcss,isnull(f.[all_amount],0) as ttl_c_dcss,isnull(g.[close_amount],0) as close_c_dcss,isnull(b.[now_all_Amount],0) as now_c_dcss from (  select count(1) as 'before_amount',cityClass from t_smd_Store s where s.StoreType = 'dcss' and   (year(s.CurrentStartDate) < (case '" + year + "' when '' then year(GETDATE()) else '" + year + "' end)   or (YEAR(s.CurrentStartDate) = (case '" + year + "' when '' then year(GETDATE()) else '" + year + "' end)   and MONTH(s.CurrentStartDate) < case '" + (month - 1) + "' when '' then MONTH(GETDATE()) else '" + (month - 1) + "' END)) and s.StoreStatus != '关闭' group by cityClass  ) as a  left join (  select count(1) as 'now_all_Amount',cityClass from t_smd_Store s where s.StoreType = 'dcss' and   (year(s.CurrentStartDate) < (case '" + year + "' when '' then year(GETDATE()) else '" + year + "' end)   or (YEAR(s.CurrentStartDate) = (case '" + year + "' when '' then year(GETDATE()) else '" + year + "' end)   and MONTH(s.CurrentStartDate) < case '" + month + "' when '' then MONTH(GETDATE()) else '" + month + "' END)) and s.StoreStatus != '关闭' group by cityClass  ) as b on a.cityClass =b.cityClass  left JOIN  (select count(1) as 'now_amount',cityClass from t_smd_Store s where s.StoreType = 'dcss' and s.IsUpgraded = 0 and s.IsDowngraded = 0  and YEAR(s.CurrentStartDate) = '" + year + "' and s.StoreStatus != '关闭' and MONTH(s.CurrentStartDate) = '" + (month - 1) + "' group by cityClass  ) as c on a.cityClass = c.cityClass  left join   (select count(1) as 'dwon_ddcn',cityClass from t_smd_Store s where s.StoreType = 'dcss' and s.IsUpgraded = 0 and s.IsDowngraded = 1  and s.BeforeDowngradedType = 'ddcn' and YEAR(s.CurrentStartDate) = '" + year + "' and MONTH(s.CurrentStartDate) = '" + (month - 1) + "'  group by cityClass  ) as d on a.cityClass = d.cityClass  left join  (select count(1) as 'up_dis',cityClass from t_smd_Store s where s.StoreType = 'dcss' and s.IsUpgraded = 1 and s.IsDowngraded = 0  and s.BeforeUpgradedType = 'dis' and YEAR(s.CurrentStartDate) = '" + year + "' and MONTH(s.CurrentStartDate) = '" + (month - 1) + "'  group by cityClass  ) as e on a.cityClass = e.cityClass   left JOIN   (select count(1) as 'all_amount',cityClass from t_smd_Store s where s.StoreType = 'dcss' and YEAR(s.CurrentStartDate) = '" + year + "' and MONTH(s.CurrentStartDate) = '" + (month - 1) + "'  group by cityClass  ) as f on a.cityClass = f.cityClass  left JOIN  (select count(1) as 'close_amount',cityClass from t_smd_Store s where s.StoreType = 'dcss' and s.StoreStatus = '关闭' and YEAR(s.CurrentStartDate) = '" + year + "' and MONTH(s.CurrentStartDate) = '" + (month - 1) + "'  group by cityClass  ) as g on a.cityClass = g.cityClass  ) as a    UNION ALL     select 'PRC' as cityClass,SUM(a.[before_amount]) as [before_amount], SUM(a.[now_amount]) as [now_amount], SUM(a.[dwon_ddcn]) as [dwon_ddcn],SUM(a.[up_dis]) AS [up_dis], SUM(a.[all_amount]) as [all_amount], SUM(a.[close_amount]) as [close_amount], SUM(a.[now_all_Amount]) as [now_all_Amount]  from (select a.cityClass,a.[before_amount],isnull(c.[now_amount],0) as [now_amount],isnull(d.[dwon_ddcn],0) [dwon_ddcn],isnull(e.[up_dis],0) as [up_dis],isnull(f.[all_amount],0) as [all_amount],isnull(g.[close_amount],0) as [close_amount],isnull(b.[now_all_Amount],0) now_all_Amount from (  select count(1) as 'before_amount',s.cityClass from t_smd_Store s where s.StoreType = 'dcss' and   (year(s.CurrentStartDate) < (case '" + year + "' when '' then year(GETDATE()) else '" + year + "' end)   or (YEAR(s.CurrentStartDate) = (case '" + year + "' when '' then year(GETDATE()) else '" + year + "' end)   and MONTH(s.CurrentStartDate) < case '" + (month - 1) + "' when '' then MONTH(GETDATE()) else '" + (month - 1) + "' END)) and s.StoreStatus != '关闭' group by cityClass  ) as a  left join (  select count(1) as 'now_all_Amount',cityClass from t_smd_Store s where s.StoreType = 'dcss' and   (year(s.CurrentStartDate) < (case '" + year + "' when '' then year(GETDATE()) else '" + year + "' end)   or (YEAR(s.CurrentStartDate) = (case '" + year + "' when '' then year(GETDATE()) else '" + year + "' end)   and MONTH(s.CurrentStartDate) < case '" + month + "' when '' then MONTH(GETDATE()) else '" + month + "' END)) and s.StoreStatus != '关闭' group by cityClass  ) as b on a.cityClass =b.cityClass  left JOIN  (select count(1) as 'now_amount',cityClass from t_smd_Store s where s.StoreType = 'dcss' and s.IsUpgraded = 0 and s.IsDowngraded = 0  and YEAR(s.CurrentStartDate) = '" + year + "' and s.StoreStatus != '关闭' and MONTH(s.CurrentStartDate) = '" + (month - 1) + "' group by cityClass  ) as c on a.cityClass = c.cityClass  left join   (select count(1) as 'dwon_ddcn',cityClass from t_smd_Store s where s.StoreType = 'dcss' and s.IsUpgraded = 0 and s.IsDowngraded = 1  and s.BeforeDowngradedType = 'ddcn' and YEAR(s.CurrentStartDate) = '" + year + "' and MONTH(s.CurrentStartDate) = '" + (month - 1) + "'  group by cityClass  ) as d on a.cityClass = d.cityClass  left join  (select count(1) as 'up_dis',cityClass from t_smd_Store s where s.StoreType = 'dcss' and s.IsUpgraded = 1 and s.IsDowngraded = 0  and s.BeforeUpgradedType = 'dis' and YEAR(s.CurrentStartDate) = '" + year + "' and MONTH(s.CurrentStartDate) = '" + (month - 1) + "'  group by cityClass  ) as e on a.cityClass = e.cityClass   left JOIN  (select count(1) as 'all_amount',cityClass from t_smd_Store s where s.StoreType = 'dcss' and YEAR(s.CurrentStartDate) = '" + year + "' and MONTH(s.CurrentStartDate) = '" + (month - 1) + "'  group by cityClass  ) as f on a.cityClass = f.cityClass  left JOIN  (select count(1) as 'close_amount',cityClass from t_smd_Store s where s.StoreType = 'dcss' and s.StoreStatus = '关闭' and YEAR(s.CurrentStartDate) = '" + year + "' and MONTH(s.CurrentStartDate) = '" + (month - 1) + "'  group by cityClass  ) as g on a.cityClass = g.cityClass  ) as a;";
            DcssC1 = clsCmm.clsDB.Query(dcssCSql);

        }

        protected void Export()
        {
            var ms = new MemoryStream();

            var year = Utils.StrToInt(AksRequest.GetFormString("year"), DateTime.Now.Year);
            clsCmm.clsDB.Storage("smdDB");
            LoadDataTable();
            using (DdcnR1)
            {
                HSSFWorkbook hssfworkbook;
                using (
                    var file = new FileStream(Server.MapPath("~/") + "Pages\\SMD\\Excel\\tracking.xls",
                        FileMode.Open, FileAccess.Read))
                {
                    hssfworkbook = new HSSFWorkbook(file);
                }

                var sheet = hssfworkbook.GetSheetAt(0);

                for (var r = 0; r < DdcnR1.Rows.Count; r++)
                {
                    for (var c = 0; c < DdcnR.Rows.Count; c++)
                    {
                        var value = DdcnR1.Rows[r][DdcnR.Rows[c]["code"].ToString()].ToString();
                        if (sheet.GetRow(1 + r) == null)
                        {
                            sheet.CreateRow(1 + r);
                        }
                        if (sheet.GetRow(1 + r)
                            .GetCell(c) == null)
                        {
                            sheet.GetRow(4 + r).CreateCell(c);
                        }
                        sheet.GetRow(4 + r)
                            .GetCell(c)
                            .SetCellValue(value);
                    }
                }

                for (var r = 0; r < DdcnC1.Rows.Count; r++)
                {
                    for (var c = 0; c < DdcnC.Rows.Count; c++)
                    {
                        var value = DdcnC1.Rows[r][DdcnC.Rows[c]["code"].ToString()].ToString();
                        if (sheet.GetRow(1 + r) == null)
                        {
                            sheet.CreateRow(1 + r);
                        }
                        if (sheet.GetRow(1 + r)
                            .GetCell(9 + c) == null)
                        {
                            sheet.GetRow(4 + r).CreateCell(9 + c);
                        }
                        sheet.GetRow(4 + r)
                            .GetCell(9+c)
                            .SetCellValue(value);
                    }
                }

                for (var r = 0; r < DcssR1.Rows.Count; r++)
                {
                    for (var c = 0; c < DcssR.Rows.Count; c++)
                    {
                        var value = DcssR1.Rows[r][DcssR.Rows[c]["code"].ToString()].ToString();
                        if (sheet.GetRow(1 + r) == null)
                        {
                            sheet.CreateRow(1 + r);
                        }
                        if (sheet.GetRow(1 + r)
                            .GetCell(c) == null)
                        {
                            sheet.GetRow(13 + r).CreateCell(c);
                        }
                        sheet.GetRow(13 + r)
                            .GetCell(c)
                            .SetCellValue(value);
                    }
                }

                for (var r = 0; r < DcssC1.Rows.Count; r++)
                {
                    for (var c = 0; c < DcssC.Rows.Count; c++)
                    {
                        var value = DcssC1.Rows[r][DcssC.Rows[c]["code"].ToString()].ToString();
                        if (sheet.GetRow(1 + r) == null)
                        {
                            sheet.CreateRow(1 + r);
                        }
                        if (sheet.GetRow(1 + r)
                            .GetCell(9 + c) == null)
                        {
                            sheet.GetRow(13 + r).CreateCell(9 + c);
                        }
                        sheet.GetRow(13 + r)
                            .GetCell(9 + c)
                            .SetCellValue(value);
                    }
                }

                hssfworkbook.Write(ms);//
                Response.AddHeader("Content-Disposition", string.Format("attachment; filename=StoreTracking" + year + ".xls"));
                Response.BinaryWrite(ms.ToArray());
                ms.Close();
                ms.Dispose();



            }
        }
    }
}